package com.sparrow.excelexporter;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;

/**
 * excel 导出为json
 * 打包 在根目录执行
 * mvn clean package
 * 执行
 * java -jar ExcelToJsonExporter.jar C:\\Users\\Administrator\\IdeaProjects\\sparrow\\example_gameserver\\src\\main\\resources\\config C:\\Users\\Administrator\\IdeaProjects\\sparrow\\example_gameserver\\src\\main\\resources\\config_export
 */
public class ExcelToJsonExporter {

    public static void main(String[] args) throws Exception {
        if (args.length < 2) {
            System.err.println("请提供输入目录和输出目录参数，例如：");
            System.err.println("java -jar ExcelToJsonExporter.jar inputDir outputDir");
            return;
        }

        String inputDir = args[0];
        String outputDir = args[1];
        exportDirectory(inputDir, outputDir);
    }

    /** 批量导出目录下的所有 Excel 文件 */
    private static void exportDirectory(String inputDir, String outputDir) throws IOException {
        File inDir = new File(inputDir);
        if (!inDir.exists() || !inDir.isDirectory()) {
            throw new IllegalArgumentException("输入目录不存在: " + inputDir);
        }

        File outDir = new File(outputDir);
        if (!outDir.exists()) {
            outDir.mkdirs();
        }

        File[] excelFiles = inDir.listFiles((dir, name) -> {
            if (name.startsWith("~$") && isExcelFile(name)) { //excel 打开的文件过滤
                return false;
            }
            return isExcelFile(name);
        });
        if (excelFiles == null || excelFiles.length == 0) {
            System.out.println("⚠️ 目录中没有找到 Excel 文件: " + inputDir);
            return;
        }

        for (File excelFile : excelFiles) {
            System.out.println("处理文件: " + excelFile.getName());
            try {
                ExcelData result = parseExcel(excelFile.getAbsolutePath());
                // 匹配.xls .xlsx ,,,x? 匹配零次或一次 x
                String jsonName = excelFile.getName().replaceAll("\\.xlsx?$", ".json");
                String jsonPath = new File(outDir, jsonName).getAbsolutePath();

                exportJson(result, jsonPath);
                System.out.println("导出完成：" + jsonPath);
            } catch (Exception e) {
                System.err.println("处理失败：" + excelFile.getName());
                e.printStackTrace();
            }
        }
    }

    private static boolean isExcelFile(String name) {
        return name.endsWith(".xlsx") || name.endsWith(".xls");
    }


    private static ExcelData parseExcel(String path) throws IOException {
        ExcelData excelData = new ExcelData();

        try (InputStream is = new FileInputStream(path);
             Workbook workbook = new XSSFWorkbook(is)) {
            Map<String, Map<String, Object>> result = new LinkedHashMap<>();
            Map<String, Object> globalResult = new LinkedHashMap<>();
            workbook.sheetIterator().forEachRemaining(sheet -> {
                // 第3行是字段名（0-based index = 2）
                Row fieldRow = sheet.getRow(2);
                if (fieldRow == null) throw new IllegalStateException("字段名行为空");
                // 第4行 字段类型
                Row typeRow = sheet.getRow(3);
                if (typeRow == null) throw new IllegalStateException("字段类型行为空");

                Cell globalCell = typeRow.getCell(0);
                if (globalCell != null && globalCell.getStringCellValue().startsWith("@Global")) {
                    excelData.global = true;
                } else {
                    excelData.global = false;
                }

                if (excelData.global) {
                    excelData.globalObj = globalResult;

                    // 从第6行开始读取数据（index=5）
                    for (int i = 5; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;
                        Cell keyCell = row.getCell(1);
                        if (keyCell == null) {
                            continue;
                        }
                        Cell valueCell = row.getCell(2);
                        String filedName = keyCell.getStringCellValue();
                        String value = getCellValue(valueCell);
                        globalResult.put(filedName, parseValue(value));
                    }
                } else {

                    excelData.objMap = result;
                    //字段名 映射 列
                    Map<String, Integer> fieldColMap = new LinkedHashMap<>();
                    for (int j = 1; j < fieldRow.getLastCellNum(); j++) { // 从第1列开始
                        Cell cell = fieldRow.getCell(j);
                        String fieldName = getCellValue(cell);
                        fieldColMap.put(fieldName, j);
                    }

                    //作为id 字段的字段名
                    String idFieldName = fieldRow.getCell(1).getStringCellValue();

                    // 从第6行开始读取数据（index=5）
                    for (int i = 5; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row == null) continue;

                        Map<String, Object> obj = new LinkedHashMap<>();
                        for (Map.Entry<String, Integer> e : fieldColMap.entrySet()) {
                            String key = e.getKey();
                            int mapCol = fieldColMap.get(key);
                            String value = getCellValue(row.getCell(mapCol));
                            obj.put(key, parseValue(value));
                        }

                        // 以 id 作为 map key
                        String id = String.valueOf(obj.get(idFieldName));
                        result.put(id, obj);
                    }
                }
            });
        }

        return excelData;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                // 注意：如果单元格是小数，会被强制转 int，可能丢精度
                return String.valueOf((int) cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    private static Object parseValue(String value) {
        if (value == null || value.isEmpty()) return "";
        try {
            if (value.contains(".")) {
                return Double.parseDouble(value);
            } else {
                long longVal = Long.parseLong(value);
                if (longVal >= Integer.MIN_VALUE && longVal <= Integer.MAX_VALUE) {
                    return (int) longVal;
                } else {
                    return longVal;
                }
            }
        } catch (NumberFormatException e) {
            return value;
        }
    }

    private static class ExcelData {
        public boolean global;
        public Map<String, Map<String, Object>> objMap;
        public Map<String, Object> globalObj;
    }

    private static void exportJson(ExcelData data, String outputPath) throws IOException {
        ObjectMapper mapper = new ObjectMapper();
        if (data.global) {
            mapper.writerWithDefaultPrettyPrinter().writeValue(new File(outputPath), data.globalObj);
        } else {
            mapper.writerWithDefaultPrettyPrinter().writeValue(new File(outputPath), data.objMap);
        }
    }
}

